package com.shketai.dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.shketai.entity.Classinfo;
import com.shketai.entity.Info;
import com.shketai.entity.Interview_info;
import com.shketai.entity.Student;
import com.shketai.entity.User;
import com.shketai.util.DateStrUtil;

public class InfoDao {

	private SessionFactory sessionFactory;
	
	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}
	/**
	 * 修改学生备注
	 * @param student
	 * @param id
	 * @return
	 */
	public void updateFlag(int id,String flags){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("update Info set flags=:flags where id=:id");
		//引号id
		query.setInteger("id",id);
		query.setString("flags", flags);
		System.out.println(id);
		System.out.println(flags);
		query.executeUpdate();
	}
	/**
	 * 批量更新缴费状态
	 * @param stu_id 需要更新的学生的id集合
	 * @param flag 缴费状态
	 */
	public void update(List<Integer> ids,int flag){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("update Info set flag=:flag where id in (:ids)");
		//根据名字设置参数
		query.setInteger("flag", flag);
		query.setParameterList("ids", ids);
		query.executeUpdate();
		
	}
	/**
	 * 退班
	 * @param id
	 */
	public void quitSubject(int id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("update Info set flag = 4 where id =:id");
		query.setInteger("id",id);
		query.executeUpdate();
	}
	
	/**
	 * 向班级里添加学生
	 * @param classinfo_id
	 * @param stu_id
	 */
	public void addInfo(int stu_id,int classinfo_id){
		Session session = sessionFactory.getCurrentSession();
		Info info = new Info();
		Classinfo ci = (Classinfo) session.get(Classinfo.class, classinfo_id);
		if(ci.getNum()>0){
		// 该班级可选人数减1
		ci.setNum(ci.getNum() - 1);
		info.setClassinfo(ci);
		Student stu = (Student) session.get(Student.class, stu_id);
		stu.setId(stu_id);
		info.setStudent(stu);
		info.setFlag(7);
		//保存信息
		session.save(info);
			
		}
	}
	public int getNum(int classinfo_id){
		int result;
		Session session = sessionFactory.getCurrentSession();
		Classinfo ci = (Classinfo) session.get(Classinfo.class, classinfo_id);
		if(ci.getNum()<=0){
			 return result = 0;
		}else{
			return result = 1;
		}
	}
	public int checkstuid(int stu_id,int classinfo_id){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Info where stu_id=? and classinfo_id=?");
		query.setInteger(0, stu_id);
		query.setInteger(1, classinfo_id);
		List<Info> list = query.list();
		if(list.size() > 0){
			return 0;
		}else{
			return 1;
		}
	}
	
	/**
	 * 检测时间段是否有冲突
	 * 
	 * @param stu_id
	 * @param classinfo_id
	 * @return true有冲突 false 无冲突
	 */
	public boolean checkPeriod(int stu_id, int classinfo_id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery(
				"select classinfo.classroominfo.day,classinfo.classroominfo.start,classinfo.classroominfo.jieshu from"
						+ " Info where student.id=:sid");
		query.setInteger("sid", stu_id);
		List<Object[]> list = query.list();
		Query query1 = session.createQuery(
				"select classroominfo.day,classroominfo.start,classroominfo.jieshu from Classinfo where id=:id");
		query1.setInteger("id", classinfo_id);
		List<Object[]> list1 = query1.list();
		Object[] period = list1.get(0);
		boolean flag = true;
		for (Object[] array : list) {
			if (period[0].equals(array[0])) {
				if (DateStrUtil.compare(period[1].toString(), array[1].toString()) == 0) {
					flag = true;
					break;
				} else if (DateStrUtil.compare(period[1].toString(), array[1].toString()) < 0) {
					if (DateStrUtil.compare(period[2].toString(), array[1].toString()) > 0) {
						flag = true;
						break;
					}
				} else {
					if (DateStrUtil.compare(period[1].toString(), array[2].toString()) < 0) {
						flag = true;
						break;
					}
				}
			}
		}
		return flag;

	}
	public Info findByStuId(List<Integer> stu_id){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Student where id in(:ids)");
		query.setParameterList("ids", stu_id);
		List<Info> list = query.list();
		//存放所有学生的id
		List<Integer> ids = new ArrayList<Integer>();
		for (Info info :list) {
			ids.add(info.getStudent().getId());
		}
		return list.get(0);
	}

	/**
	 * 我的课程
	 * @param user_id
	 * @return
	 */
	public List<Info> find(int user_id){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Student where user.id = ?");
		query.setLong(0, user_id);
		List<Student> list = query.list();
		//存放所有学生的id
		List<Integer> ids = new ArrayList<Integer>();
		for (Student student :list) {
			ids.add(student.getId());
		}
		//这一条hql语句相当于  select * from info where stu_id in(1,2,90)，就是查询这些学生的选课信息
		Query query2 = session.createQuery("from Info where student.id in(:ids) ");
		query2.setParameterList("ids",ids);
		List<Info> list1 = query2.list();
		return list1;
	}
	
	public List<Info> findById(int stu_id){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Info where stu_id=?");
		query.setInteger(0, stu_id);
		List<Info> list = query.list();
		return list;
	}
	
	/**
	 * 删除选课
	 * @param id
	 * @return
	 */
	public int deleteById(int id){
		Session session = sessionFactory.getCurrentSession();
		Info info = new Info();
		info.setId(id);
		System.out.println(id);
		session.delete(info);
		return 1;
	}
	

	/**
	 * 显示出已选这门课的学生
	 * @param classinfo_id
	 * @return
	 */
	public List<Info> findByClassinfoId(int classinfo_id,int page){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Info where classinfo_id=?");
		query.setInteger(0, classinfo_id);
		query.setFirstResult((page-1)*10);
		query.setMaxResults(10);
		Classinfo classinfo = new Classinfo();
		classinfo.setId(classinfo_id);
		Info info = new Info();
		info.setId(classinfo_id);
		List<Info> list = query.list();
		if(list.size() > 0){
			return list;
		}else{
			return null;
		}
	}
	
	public List<Info> findByClassinfoId1(int classinfo_id,int page){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery(
				"SELECT t5.id,t5.stuName,t5.sex,t5.personalNum,t6.classname ,t4.classinfo_id FROM info t4 INNER JOIN student t5 on t4.stu_id = t5.id INNER JOIN classinfo t6 on t4.classinfo_id = t6.id WHERE t4.stu_id NOT IN ("
		+"SELECT t2.id FROM info t1 INNER JOIN student t2 ON t1.stu_id = t2.id "
		+"INNER JOIN attence t3 ON t3.stu_id = t2.id WHERE t1.classinfo_id = ?) and t4.classinfo_id = ?");
		query.setInteger(0, classinfo_id);
		query.setInteger(1, classinfo_id);
		query.setFirstResult((page-1)*10);
		query.setMaxResults(10);
		
		List<Object[]> list = query.list();
		List<Info> result = new ArrayList<Info>();
		for (Object[] array : list) {
			Info info = new Info();
			Student student = new Student();
			student.setId(Integer.parseInt(array[0].toString()));
			student.setStuName(array[1].toString());
			student.setSex(Integer.parseInt(array[2].toString()));
			student.setPersonalNum(array[3].toString());
			Classinfo classinfo = new Classinfo();
			classinfo.setClassname(array[4].toString());
			classinfo.setId(Integer.parseInt(array[5].toString()));
			info.setStudent(student);
			info.setClassinfo(classinfo);
			result.add(info);
		}
		return result;
	}
	
	public List<Info> findByClassinfoIds(int classinfo_id,int page){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Info where classinfo_id=?");
		query.setInteger(0, classinfo_id);
		query.setFirstResult((page-1)*10);
		query.setMaxResults(10);
		Classinfo classinfo = new Classinfo();
		classinfo.setId(classinfo_id);
		Info info = new Info();
		info.setId(classinfo_id);
		List<Info> list = query.list();
		if(list.size() > 0){
			return list;
		}else{
			return null;
		}
	}
	
	public List<Info> findAll(int classinfo_id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Info where classinfo_id=?");
		query.setInteger(0, classinfo_id);
		Classinfo classinfo = new Classinfo();
		classinfo.setId(classinfo_id);
		Info info = new Info();
		info.setId(classinfo_id);
		List<Info> list = query.list();
		if(list.size() > 0){
			return list;
		}else{
			return null;
		}
	}
	
	/**
	 * 查出该班级下的学生
	 * @param classinfo_id
	 * @return
	 */
	public List<Object[]> findAllByclassId(int classinfo_id){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery("select t.`subject` ,c.classname ,s.stuName,s.sex,s.personalNum,s.birthday,s.address,s.postcode,s.parent,s.mobilephone,s.emergencyContactName,s.emergencyContactPhone,s.workaddress,s.schoolname from student s, Info i ,`subject` t ,classinfo c where s.id=i.stu_id AND c.id=i.classinfo_id AND t.id=c.subject_id AND classinfo_id=?");
		query.setInteger(0, classinfo_id);
		List<Object[]> list = query.list();
		if(list.size() > 0){
			return list;
		}else{
			return null;
		}
	}
	
	public List<Object[]> findAllBySubject(){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery(" select t.`subject` ,c.classname ,s.stuName,s.sex,s.personalNum,s.birthday,s.address,s.postcode,s.parent,s.mobilephone,s.emergencyContactName,s.emergencyContactPhone,s.workaddress,s.schoolname from student s, Info i ,`subject` t ,classinfo c where s.id=i.stu_id AND c.id=i.classinfo_id AND t.id=c.subject_id ORDER BY c.id");
		List<Object[]> list = query.list();
		if(list.size() > 0){
			return list;
		}else{
			return null;
		}
	}
	public int findCount(int classinfo_id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("select count(*) from Info where classinfo_id=?");
		query.setInteger(0, classinfo_id);
		Long total = (Long) query.uniqueResult();
		return total.intValue();
	}
 
	
	public int findCount1(int classinfo_id) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery("SELECT count(*) FROM info t4  WHERE t4.stu_id NOT IN ("
		+"SELECT t2.id FROM info t1 "
		+"INNER JOIN student t2 ON t1.stu_id = t2.id "
		+"INNER JOIN attence t3 ON t3.stu_id = t2.id WHERE t1.classinfo_id = ?)and t4.classinfo_id = ?");
		query.setInteger(0, classinfo_id);
		query.setInteger(1, classinfo_id);
		BigInteger total = (BigInteger) query.uniqueResult();
		return total.intValue();
	}

	public List<Student> findById1(int id){ 
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Student where id=?");
		query.setInteger(0, id);
		List<Student> list = query.list();
		if(list.size() > 0){
			return list;
		}else{
			return null;
		}
	}
	
	/**
	 * 缴费时候查询学生选课信息
	 * @param user_id
	 * @param classinfo_id
	 * @return
	 */
	public List<Info> findAllInfo(int user_id){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Student where user.id = ?");
		query.setLong(0, user_id);
		List<Student> list = query.list();
		//存放所有学生的id
		List<Integer> ids = new ArrayList<Integer>();
		for (Student student :list) {
			ids.add(student.getId());
		}
		//这一条hql语句相当于  select * from info where stu_id in(1,2,90)，就是查询这些学生的选课信息
		Query query2 = session.createQuery("from Info where student.id in(:ids) and flag=1");
		query2.setParameterList("ids",ids);
		List<Info> infos = query2.list();
		return infos;
	}
	
	/**
	 * 退费时候查询学生选课信息
	 * @param user_id
	 * @param classinfo_id
	 * @return
	 */
	public List<Info> findAllInfo1(int user_id){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Student where user.id = ?");
		query.setLong(0, user_id);
		List<Student> list = query.list();
		//存放所有学生的id
		List<Integer> ids = new ArrayList<Integer>();
		for (Student student :list) {
			ids.add(student.getId());
		}
		//这一条hql语句相当于  select * from info where stu_id in(1,2,90)，就是查询这些学生的选课信息
		Query query2 = session.createQuery("from Info where student.id in(:ids) and flag=3");
		query2.setParameterList("ids",ids);
		List<Info> infos = query2.list();
		return infos;
	}
	
	/**
	 * 续费时候查询学生选课信息
	 * @param user_id
	 * @param classinfo_id
	 * @return
	 */
	public List<Info> findAllInfo2(int user_id){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Student where user.id = ?");
		query.setLong(0, user_id);
		List<Student> list = query.list();
		//存放所有学生的id
		List<Integer> ids = new ArrayList<Integer>();
		for (Student student :list) {
			ids.add(student.getId());
		}
		//这一条hql语句相当于  select * from info where stu_id in(1,2,90)，就是查询这些学生的选课信息
		Query query2 = session.createQuery("from Info where student.id in(:ids) and flag=7");
		query2.setParameterList("ids",ids);
		List<Info> infos = query2.list();
		return infos;
	}
	/**
	 * 缴费审核查询学生选课信息
	 * @param user_id
	 * @param classinfo_id
	 * @return
	 */
	public List<Info> findAllInfo4(int page){
		Session session = sessionFactory.getCurrentSession();
		/*Query query = session.createQuery("from Student where id = ?");
		query.setLong(0, id);
		List<Student> list = query.list();
		//存放所有学生的id
		List<Integer> ids = new ArrayList<Integer>();
		for (Student student :list) {
			ids.add(student.getId());
		}*/
		//这一条hql语句相当于  select * from info where stu_id in(1,2,90)，就是查询这些学生的选课信息
		Query query2 = session.createQuery("from Info where flag=2");
		query2.setFirstResult((page-1)*10);
		query2.setMaxResults(10);
		//query2.setParameterList("ids",ids);
		List<Info> infos = query2.list();
		return infos;
	}
	
	public List<Info> queryByUsername1(String stuName){
		Session session = sessionFactory.getCurrentSession();
		List<Info> list = session.createQuery("from Info where flag = 2 and student.stuName like ? ").setString(0,"%"+stuName+"%").list();
		return list;
	}
	
	public List<Info> queryByUsername2(String stuName){
		Session session = sessionFactory.getCurrentSession();
		List<Info> list = session.createQuery("from Info where flag = 8 and student.stuName like ? ").setString(0,"%"+stuName+"%").list();
		return list;
	}
	
	public List<Info> queryByUsername3(String stuName){
		Session session = sessionFactory.getCurrentSession();
		List<Info> list = session.createQuery("from Info where flag = 5 and student.stuName like ? ").setString(0,"%"+stuName+"%").list();
		return list;
	}
	/**
	 * 续费审核查询学生选课信息
	 * @param user_id
	 * @param classinfo_id
	 * @return
	 */
	public List<Info> findAllInfo5(int page){
		Session session = sessionFactory.getCurrentSession();
		/*Query query = session.createQuery("from Student where id = ?");
		query.setLong(0, id);
		List<Student> list = query.list();
		//存放所有学生的id
		List<Integer> ids = new ArrayList<Integer>();
		for (Student student :list) {
			ids.add(student.getId());
		}*/
		//这一条hql语句相当于  select * from info where stu_id in(1,2,90)，就是查询这些学生的选课信息
		Query query2 = session.createQuery("from Info where flag=8");
		query2.setFirstResult((page-1)*10);
		query2.setMaxResults(10);
		List<Info> infos = query2.list();
		return infos;
	}
	
	public List<Info> findAllInfo6(int page){
		Session session = sessionFactory.getCurrentSession();
		/*Query query = session.createQuery("from Student where id = ?");
		query.setLong(0, id);
		List<Student> list = query.list();
		//存放所有学生的id
		List<Integer> ids = new ArrayList<Integer>();
		for (Student student :list) {
			ids.add(student.getId());
		}*/
		//这一条hql语句相当于  select * from info where stu_id in(1,2,90)，就是查询这些学生的选课信息
		Query query2 = session.createQuery("from Info where flag=5");
		query2.setFirstResult((page-1)*10);
		query2.setMaxResults(10);
		List<Info> infos = query2.list();
		return infos;
	}
	/**
	 * 退班时查询学生所选班级信息
	 * @param user_id
	 * @return
	 */
	public List<Info> findAllInfo3(int user_id){
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("from Student where user.id = ?");
		query.setLong(0, user_id);
		List<Student> list = query.list();
		//存放所有学生的id
		List<Integer> ids = new ArrayList<Integer>();
		for (Student student :list) {
			ids.add(student.getId());
		}
		//这一条hql语句相当于  select * from info where stu_id in(1,2,90)，就是查询这些学生的选课信息
		Query query2 = session.createQuery("from Info where student.id in(:ids) and (flag=1 or flag=3 or flag=2)");
		query2.setParameterList("ids",ids);
		List<Info> infos = query2.list();
		return infos;
	}
	public int findCount() {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("select count(*) from Info where flag = 2");
		Long total = (Long) query.uniqueResult();
		return total.intValue();
	}
	
	public int findCount1() {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("select count(*) from Info where flag = 1");
		Long total = (Long) query.uniqueResult();
		return total.intValue();
	}
	
	public int findCount2() {
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery("select count(*) from Info where flag = 5");
		Long total = (Long) query.uniqueResult();
		return total.intValue();
	}
}